using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace Calendar
{
    public partial class Form1 : Form
    {
        List<Appointment> calendarAppointments;

        List<AppointmentWrapper> appointments = new List<AppointmentWrapper>();

        List<Patient> patients = new List<Patient>();

        List<Physician> physicians = new List<Physician>();

        SQLiteConnection con;

        String SQL_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";

        public Form1()
        {
            InitializeComponent();

            calendarAppointments = new List<Appointment>();

            dayView1.StartDate = DateTime.Now;
            dayView1.ResolveAppointments += new Calendar.ResolveAppointmentsEventHandler(this.dayView1_ResolveAppointments);

            comboBox1.DataSource = physicians;
            comboBox2.DataSource = patients;

            con = new SQLiteConnection("Data Source=siwoz.db;Version=3;New=False;Compress=True;");
            con.Open();

            FetchPatients();
            FetchPhysicians();
            FetchAppointments();
        }

        private void dayView1_ResolveAppointments(object sender, ResolveAppointmentsEventArgs args)
        {
            List<Appointment> m_Apps = new List<Appointment>();

            foreach (Appointment m_App in calendarAppointments)
                if ((m_App.StartDate >= args.StartDate) && (m_App.StartDate <= args.EndDate))
                {
                    m_Apps.Add(m_App);
                }

            args.Appointments = m_Apps;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Appointment m_App = new Appointment();
            Physician physician = (Physician)comboBox1.SelectedItem;
            Patient patient = (Patient)comboBox2.SelectedItem;
            m_App.StartDate = dayView1.SelectionStart;
            m_App.EndDate = dayView1.SelectionEnd;
            m_App.Color = getColor(comboBox1.SelectedIndex + 1, 5);
            m_App.Group = physician.Name;
            m_App.Title = physician.Name + " " + patient.Name;

            SaveAppointment(m_App, physician.Id, patient.Id);
            calendarAppointments.Add(m_App);

            dayView1.Invalidate();
        }

        private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
        {
            dayView1.StartDate = monthCalendar1.SelectionStart;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (dayView1.SelectedAppointment != null && !dayView1.SelectedAppointment.Locked)
            {
                AppointmentWrapper toDelete = null;
                foreach (var appointment in appointments)
                {
                    if (appointment.Appointment == dayView1.SelectedAppointment)
                    {
                        toDelete = appointment;
                    }
                }

                calendarAppointments.Remove(dayView1.SelectedAppointment);
                appointments.Remove(toDelete);
                RemoveAppointment(toDelete);
                dayView1.Invalidate();
            }
        }

        private static Color getColor(int value, int scale)
        {
            KnownColor[] names = (KnownColor[])Enum.GetValues(typeof(KnownColor));
            KnownColor randomColorName = names[names.Length * value / scale];
            Color randomColor = Color.FromKnownColor(randomColorName);
            return randomColor;
        }

        private void FetchPatients()
        {
            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM patients";
            SQLiteDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();

            patients = new List<Patient>();
            foreach (DataRow row in dt.Rows)
            {
                patients.Add(new Patient() { Id = Int32.Parse(row[0].ToString()), Name = row[1].ToString(), BirthYear = row[2].ToString(), });
            }
            comboBox2.DataSource = patients;
        }

        private void FetchAppointments()
        {
            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM appointments";
            SQLiteDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();

            foreach (DataRow row in dt.Rows)
            {
                AppointmentWrapper appointment = new AppointmentWrapper()
                {
                    Id = Int32.Parse(row[0].ToString()),
                    PhysicianId = Int32.Parse(row[1].ToString()),
                    PatientId = Int32.Parse(row[2].ToString()),
                };
                String physician = FindPerson(appointment.PhysicianId, true);
                String patient = FindPerson(appointment.PatientId, false);
                
                Appointment calendarAppointment = new Appointment() 
                {
                    Title = physician + "\n" + patient,
                    Group = physician,
                    StartDate = DateTime.Parse(row[3].ToString()),
                    EndDate = DateTime.Parse(row[4].ToString()),
                    Color = getColor(appointment.PhysicianId/10, 5),
                };
                appointment.Appointment = calendarAppointment;

                appointments.Add(appointment);
                calendarAppointments.Add(calendarAppointment);
                dayView1.Invalidate();
            }
            comboBox1.DataSource = physicians;
        }

        private string FindPerson(int id, bool physician)
        {
            String table = physician ? "physicians" : "patients";
            String sql = "SELECT name FROM " + table + " WHERE id=" + id;

            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            SQLiteDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            return dt.Rows[0][0].ToString();
        }

        private void FetchPhysicians()
        {
            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM physicians";
            SQLiteDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);

            physicians = new List<Physician>();
            foreach (DataRow row in dt.Rows)
            {
                physicians.Add(new Physician() { Id = Int32.Parse(row[0].ToString()), Name = row[1].ToString(), });
            }
            comboBox1.DataSource = physicians;

            reader.Close();
        }

        private void SaveAppointment(Appointment calendarAppointment, int physicianId, int patientId)
        {
            AppointmentWrapper appointment = new AppointmentWrapper();
            appointment.Id = FetchNextId();
            appointment.PhysicianId = physicianId;
            appointment.PatientId = patientId;
            appointment.Appointment = calendarAppointment;

            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("INSERT INTO appointments VALUES({0}, {1}, {2}, '{3}', '{4}', null)",
                appointment.Id,
                appointment.PhysicianId,
                appointment.PatientId,
                calendarAppointment.StartDate.ToString(SQL_DATE_FORMAT),
                calendarAppointment.EndDate.ToString(SQL_DATE_FORMAT));
            int rows = cmd.ExecuteNonQuery();
        }

        private int FetchNextId()
        {
            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT MAX(id) FROM appointments";
            SQLiteDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            return Int32.Parse(dt.Rows[0][0].ToString()) + 10;
        }

        private void RemoveAppointment(AppointmentWrapper appointment)
        {
            using (var transaction = con.BeginTransaction())
            {
                SQLiteCommand cmd = con.CreateCommand();
                cmd.CommandText = "DELETE FROM appointments WHERE id=" + appointment.Id;
                int count = cmd.ExecuteNonQuery();
                transaction.Commit();
            }
        }

        private void dayView1_AppointmentMove(object sender, AppointmentEventArgs e)
        {
            AppointmentWrapper updated = null;
            foreach (var appointment in appointments)
            {
                if (appointment.Appointment == e.Appointment)
                {
                    updated = appointment;
                }
            }

            using (var transaction = con.BeginTransaction())
            {
                SQLiteCommand cmd = con.CreateCommand();
                String startTime = "visit_start='" + e.Appointment.StartDate.ToString(SQL_DATE_FORMAT) + "'";
                String endTime = "visit_end='" + e.Appointment.EndDate.ToString(SQL_DATE_FORMAT) + "'";
                cmd.CommandText = String.Format("UPDATE appointments SET {0}, {1} WHERE id={2}", startTime, endTime, updated.Id);
                int count = cmd.ExecuteNonQuery();
                transaction.Commit();
            }
           
            Console.WriteLine("move");
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            con.Close();
        }

    }
}